package com.ktwlrj.core.util.xpoi;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

/**
 * work book工具类
 *
 * @author zhouxuanhong
 * @date 2019-08-15 11:21
 * @since JDK1.8
 */
public class WorkbookUtils {
    /**
     * excel模板比较器
     *
     * @param template ${@link InputStream}
     * @param data     ${@link InputStream}
     * @throws IOException            ${@link IOException}
     * @throws InvalidFormatException ${@link InvalidFormatException}
     * @author zhouxuanhong
     * @date 2019-08-15 11:57
     */
    public static boolean compareToTemplate(InputStream template, InputStream data) throws IOException, InvalidFormatException {
        Workbook templateWorkbook = WorkbookFactory.create(template);
        Workbook dataWorkbook = WorkbookFactory.create(data);
        return compareToTemplate(templateWorkbook, dataWorkbook);
    }


    /**
     * excel模板比较器
     *
     * @param templateWorkbook ${@link Workbook} 模板Workbook
     * @param dataWorkbook     ${@link Workbook} 数据Workbook
     * @author zhouxuanhong
     * @date 2019-08-15 11:25
     */
    public static boolean compareToTemplate(Workbook templateWorkbook, Workbook dataWorkbook) {
        int templateSheetNum = templateWorkbook.getNumberOfSheets();
        int dataSheetNum = dataWorkbook.getNumberOfSheets();
        System.out.println("----------WorkbookUtils---Sheet1------->compareToTemplate:" + templateSheetNum);
        System.out.println("----------WorkbookUtils---Sheet2------->compareToTemplate:" + dataSheetNum);
        if (templateSheetNum != dataSheetNum) {
            return false;
        }
        for (int i = 0; i < templateSheetNum; i++) {
            Sheet templateSheet = templateWorkbook.getSheetAt(i);
            Sheet dataSheet = dataWorkbook.getSheetAt(i);
            int rowNum = templateSheet.getLastRowNum();
            for (int rowIdx = 0; rowIdx <= rowNum; rowIdx++) {
                Row templateRow = templateSheet.getRow(rowIdx);
                Row dataRow = dataSheet.getRow(rowIdx);
                if (templateRow != null && dataRow == null) {
                    return false;
                }
                if (templateRow == null) {
                    continue;
                }
                // 获得总列数
                int templateColumnNum = templateRow.getPhysicalNumberOfCells();
                for (int colIdx = 0; colIdx < templateColumnNum; colIdx++) {
                    // 获取单元格
                    Cell templateCell = templateRow.getCell(colIdx);
                    if (templateCell == null) {
                        continue;
                    }
                    // 单元格值
                    String templateValue = getCellValue(templateCell).trim();
                    String dataValue = getCellValue(dataRow.getCell(colIdx)).trim();
                    // 单元格内容不满足模板要求
                    if (StringUtils.isBlank(templateValue) || templateValue.contains("&=") || templateValue.equals("万科麓山")) {
                        continue;
                    }
                    if (!templateValue.equals(dataValue)) {
                        return false;
                    }
                }
            }
        }
        return true;
    }


    private static String getCellValue(Cell cell) {
        String cellValue = "";
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                // 处理日期格式、时间格式
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    if (date != null) {
                        cellValue = cell.getDateCellValue().toString();
                    }
                    //处理数值格式
                } else if (cell.getCellStyle().getDataFormat() == 0) {
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING:
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
            case ERROR:
            default:
                cellValue = "";
        }
        return cellValue;
    }
}
